package com.neuedu.corejava.jdbc.dao.impl;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.neuedu.corejava.jdbc.DBHelper;
import com.neuedu.corejava.jdbc.bean.User1;
import com.neuedu.corejava.jdbc.dao.IUser1Dao;

public class User1DaoImpl implements IUser1Dao {
	
	@Override
	public List<User1> finAll() {
		List<User1> list = new ArrayList<User1>();
		//打开连接
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rSet = null;
		try {
			//执行sql
			String sql="select * from user1";
			stmt = conn.prepareStatement(sql);
			rSet = stmt.executeQuery();
			while (rSet.next()) {
				int id = rSet.getInt("id");
				String name = rSet.getString("name");
				String pwd = rSet.getString("pwd");
				String email = rSet.getString("email");
				Date date = rSet.getTimestamp("birthDate");
				User1 user = new User1(id,name,pwd,email,date);
				list.add(user);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rSet, stmt, conn);
		}	
		return list;
	}

	
	@Override
	public User1 finById(int id) {
		User1 user1=null;
		Connection conn=DBHelper.openConnection();
		PreparedStatement stmt=null;
		ResultSet rSet = null ;
		try {
			String sql="select id,name,pwd,email,birthdate from user1 where id=?";
			stmt=conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rSet=stmt.executeQuery();
			if(rSet.next()){
				int id1=rSet.getInt("id");
				String name=rSet.getString("name");
				String pwd=rSet.getString("pwd");
				String email=rSet.getString("email");
				Date birthdate=rSet.getTimestamp("birthDate");
				user1=new User1( id1,name,pwd,email,birthdate);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rSet, stmt, conn);
		}
	
		return user1;
	}

	
	@Override
	public List<User1> finByName(User1 paT) {
		List<User1> list=new ArrayList<User1>();
		Connection conn=DBHelper.openConnection();
	
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			String sql="select id,name,pwd,email,birthdate from user1 where name like ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "%"+paT.getName()+"%");
			rs=stmt.executeQuery();
			while(rs.next()) {
				int id=rs.getInt("id");
				String name=rs.getString("name");
				String pwd=rs.getString("pwd");
				String email=rs.getString("email");
				Date birthdate=rs.getTimestamp("birthdate");
				User1 user1=new User1(id,name,pwd,email,birthdate);
				list.add(user1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		
		return list;
		
	}
	

	@Override
	public boolean add(User1 param) {
		Connection conn=DBHelper.openConnection();
		PreparedStatement stmt=null;
		//ResultSet rs=null;
		try {
			String sql="insert into user1 (id,name,pwd,email,birthdate) values(SEQ_USER1.NEXTVAL,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
		   	stmt.setString(1, param.getName());
			stmt.setString(2, param.getPwd());
			stmt.setString(3, param.getEmail());
		   	stmt.setTimestamp(4,  new java.sql.Timestamp(param.getBirthDate().getTime()));
		   	
		   	int count=stmt.executeUpdate();
		   	if(count == 1) {
		   		return true;
		   	}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		
		return false;
	}

	@Override
	public boolean edit(User1 param) {
		Connection conn =DBHelper.openConnection();
		PreparedStatement stmt=null;
		
		try {
			String sql="update user1 set pwd=? ,email=?,birthdate=? where id=?";
			stmt =conn.prepareStatement(sql);
			stmt.setInt(4,param.getId());
			stmt.setString(1, param.getPwd());
			stmt.setString(2, param.getEmail());
			stmt.setTimestamp(3, new java.sql.Timestamp(param.getBirthDate().getTime()));
			int count=stmt.executeUpdate();
			if(count==1) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}

	@Override
	public boolean deleteById(int id) {
		Connection conn=DBHelper.openConnection();
		PreparedStatement stmt=null;
		
		try {
			String sql="delete from user1  where id=?";
			stmt=conn.prepareStatement(sql);
			stmt.setInt(1, id);
			int count = stmt.executeUpdate();
			if(count==1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		
		return false;
	}


	


	
	

}
